from sqlalchemy.orm import Session
from sqlalchemy import and_, or_, func
from config import Base, engine
from sqlalchemy import Column, Integer, String, Boolean, DateTime
# 用户模型定义
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, autoincrement=True, comment="用户ID")
    username = Column(String(50), nullable=False, unique=True, index=True, comment="用户名")
    email = Column(String(100), nullable=False, unique=True, index=True, comment="邮箱")
    hashed_password = Column(String(255), nullable=False, comment="加密密码")
    full_name = Column(String(100), nullable=True, index=True, comment="全名")
    is_active = Column(Boolean, default=True, comment="是否激活")
    is_admin = Column(Boolean, default=False, comment="是否为管理员")
    created_at = Column(DateTime, default=func.current_timestamp(), comment="创建时间")
    updated_at = Column(DateTime, onupdate=func.current_timestamp(), comment="更新时间")

    def __repr__(self):
        return f"<User(id={self.id}, username='{self.username}', email='{self.email}')>"

    def to_dict(self):
        """转换为字典，方便JSON序列化"""
        return {
            "id": self.id,
            "username": self.username,
            "email": self.email,
            "full_name": self.full_name,
            "is_active": self.is_active,
            "is_admin": self.is_admin,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None
        }

# 初始化表结构
def init_db():
    Base.metadata.create_all(bind=engine)

# CRUD操作
def create_user(db: Session, username: str, email: str, hashed_password: str, full_name: str = None):
    """创建新用户"""
    db_user = User(
        username=username,
        email=email,
        hashed_password=hashed_password,
        full_name=full_name
    )
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user

def get_user_by_id(db: Session, user_id: int):
    """通过ID查询用户"""
    return db.query(User).filter(User.id == user_id).first()

def get_user_by_username(db: Session, username: str):
    """通过用户名查询用户"""
    return db.query(User).filter(User.username == username).first()

def get_user_by_email(db: Session, email: str):
    """通过邮箱查询用户"""
    return db.query(User).filter(User.email == email).first()

def update_user(db: Session, user_id: int, **kwargs):
    """更新用户信息"""
    user = db.query(User).filter(User.id == user_id).first()
    if user:
        for key, value in kwargs.items():
            if hasattr(user, key):
                setattr(user, key, value)
        db.commit()
        db.refresh(user)
    return user

def delete_user(db: Session, user_id: int):
    """删除用户"""
    user = db.query(User).filter(User.id == user_id).first()
    if user:
        db.delete(user)
        db.commit()
    return user

def get_users(db: Session, skip: int = 0, limit: int = 10, **filters):
    """查询用户列表，支持过滤、分页"""
    query = db.query(User)
    # 应用过滤条件
    for key, value in filters.items():
        if hasattr(User, key):
            query = query.filter(getattr(User, key) == value)

    return query.offset(skip).limit(limit).all()

def get_active_users(db: Session):
    """查询所有活跃用户"""
    return db.query(User).filter(User.is_active == True).all()

def search_users(db: Session, keyword: str):
    """根据关键字搜索用户（用户名或邮箱包含关键字）"""
    return db.query(User).filter(
        or_(
            User.username.like(f"%{keyword}%"),
            User.email.like(f"%{keyword}%"),
            User.full_name.like(f"%{keyword}%")
        )
    ).all()
